package com.example.fw;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

//import com.example.test.ContactObject;

public class JdbcHelper extends HelperBase{

	private Connection conn;
	
	public JdbcHelper(ApplicationManager manager) {
		super(manager);
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		try {
			conn = DriverManager.getConnection(manager.getProperty("db.url"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String getLastGroupId() {
		Statement st = null;
		ResultSet res = null;
		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT MAX(group_id) FROM group_list");
			if (res.next()) {
				return res.getString(1);
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public Set<GroupObject> listGroups() {
		Set<GroupObject> groups = new HashSet<GroupObject>();
		Statement st = null;
		ResultSet res = null;
		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT group_id,group_name,group_header,group_footer FROM group_list");
			while (res.next()) {
				groups.add(new GroupObject().setId(res.getString(1)).setName(res.getString(2)).setHeader(res.getString(3)).setFooter(res.getString(4)));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return groups;
	}

	public String getLastContactId() {
		Statement st = null;
		ResultSet res = null;
		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT MAX(contact_id) FROM contact_list");
			if (res.next()) {
				return res.getString(1);
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public Set<ContactObject> listContacts() {
		Set<ContactObject> contacts = new HashSet<ContactObject>();
		Statement st = null;
		ResultSet res = null;
		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT id,firstName,lastName,address,home,mobile,work,email,email2,bday,bmonth,byear," +
					"address2,phone2 FROM contact_list");
			while (res.next()) {
				contacts.add(new ContactObject().setId(res.getString(1)).setFirstName(res.getString(2)).setLastName(res.getString(3)).setAddress(res.getString(4)).
						setHome(res.getString(5)).setMobile(res.getString(6)).setWork(res.getString(7)).setEmail(res.getString(8)).
						setEmail2(res.getString(9)).setBday(res.getString(10)).setBmonth(res.getString(11)).setByear(res.getString(12)).
						setAddress2(res.getString(13)).setPhone2(res.getString(14)));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return contacts;
	}
}
